package mysql.swing;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import mysql.swing.JFrame.MyFrame;

public class MyConnect {

	private String url;
	private String user;
	private String password;
	private Connection connection;

	public void connect() {

		Properties p = new Properties();
		try {
			p.load(new FileInputStream("config.properties"));
			url = p.getProperty("url");
			user = p.getProperty("user");
			password = p.getProperty("password");
			
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(url, user, password);
			
			System.out.println("Connection success");

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		if (connection == null) {
			throw new NullPointerException("Null connection");
		}
	}

	public ResultSet getData() {
		ResultSet resultSet = null;
		String sqlCommand = "Select * from test";
		try {
			Statement statement = connection.createStatement();
			resultSet = statement.executeQuery(sqlCommand);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultSet;
	}

	public ResultSet getData(String id) {
		ResultSet resultSet = null;
		String sqlCommand = "Select * from test where id = '" + id + "'";
		try {
			Statement statement = connection.createStatement();
			resultSet = statement.executeQuery(sqlCommand);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultSet;
	}

	public ResultSet getDataPre(String id) {
		ResultSet resultSet = null;
		String sqlCommand = "Select * from test where id = ?";
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sqlCommand);
			statement.setString(1, id);
			resultSet = statement.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultSet;
	}

	public void showData(ResultSet resultSet) {
		try {
			while (resultSet.next()) {
				System.out.printf("%10s %20s %-5.2f \n",
						resultSet.getString(1), // ( + or - ) 5.2 is all ok
						resultSet.getString(2), resultSet.getFloat(3));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void deleteId(String id) {
		String sqlCommand = "delete from test where id = ?";
		PreparedStatement pst = null;
		try {
			pst = connection.prepareStatement(sqlCommand);
			pst.setString(1, id);
			int check = pst.executeUpdate();
			if (check == 1) {
				System.out.println("Delete success");
			} else {
				System.out.println("Delete fail");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void insert(Student s) {
		String sqlCommand = "insert into test values(?,?,?)";
		PreparedStatement pst = null;
		try {
			pst = connection.prepareStatement(sqlCommand);
			pst.setString(1, s.getId());
			pst.setString(2, s.getName());
			pst.setFloat(3, s.getPoint());
			int check = pst.executeUpdate();
			if (check == 1) {
				System.out.println("Insert success");
			} else {
				System.out.println("Insert fail");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void update(String id, Student s) {
		String sqlCommand = "update test set name = ? , point = ? where id = ?";
		System.out.println(id + " " + s.getId() + " " + s.getName() + " " + s.getPoint());
		PreparedStatement pst = null;
		try {
			pst = connection.prepareStatement(sqlCommand);
			pst.setString(1, s.getName());
			pst.setFloat(2, s.getPoint());
			pst.setString(3, s.getId());
			int check = pst.executeUpdate();
			if (check == 1) {
				System.out.println("update success");
			} else {
				System.out.println("update fail");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}


}
